#!/use/bin/python
# coding=utf-8
# 早间开机 数据分析-追剧提醒-服务推送成功率
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql):
    conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num


#根据月份 获取本月指定指标的 总和
#tableName 指定的报表
#columnName 指定的指标字段
#date 月份
def getSumNumByTableColumnNameAndDate(tableName,columnName,date):
    conn, cur = getDataConnection()
    selectNumSql = f"""
                     select sum({columnName}) from {tableName} t where 1=1   
                     """;
    if len(date) >0:
        selectNumSql = f"""{selectNumSql} and SUBSTR( t.ref_date, 1,7) = '{date}' and LENGTH(t.ref_date) = 10  """
    cur.execute(selectNumSql)
    #print(selectNumSql);
    numResult = cur.fetchone()
    num = 0
    if numResult!= None:
        num = numResult[0]
    if num is None:
        return 0
    return num

#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if(X is None or Y is None):
        return rate
    if Y != 0:
        if X > Y:
            rate = 100
        else:
            rate = round(X / Y, 4) * 100
    return rate

# 记录 数据分析-早间开机-服务推送成功率 analysis_zjtx_push_rate
def insertPushRate(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zjtx_push_rate 表是否已经存在当天 数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_zjtx_push_rate t where 1=1  and t.ref_date =  '{date}'")
        #消息等待展示/追了剧且当日剧集有更新的设备量
        moviesUpdateNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'suNewsWaitShow' and  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}'")
        # 获取早间开机订阅数
        subscribeNum = selectNumBySql(
            f"select cumulate_num from analysis_subscribe_number t where t.service_key ='zjtx' and t.ref_date =  '{date}' and t.execute_flag = 1 ")
        if subscribeNum == 0:
            subscribeNum = selectNumBySql(
                f"select count(1) subNum from life_assistant_device t where (switches_state1 & (1 << (7 - 1)))<>0 and 	(switches_state1 & (1 << (8 - 1)))<>0 and (switches_state1 & (1 << (14 - 1)))<>0 ", 1)
        # 获取当日追剧提醒 展示次数
        newPushNum = selectNumBySql(
            f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'suAutoOpenScreen' and  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}'")
        # 获取当日追剧提醒 展示设备量(去重)
        newActiveIdNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) from log_common_service_{yyMMddDate} t where t.`key` = 'suAutoOpenScreen' and  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}'")
        #当日开机率
        showRate = getRateByXY(newActiveIdNum, subscribeNum)
        # 获取所有追剧提醒 展示次数
        cumulatePushNum = selectNumBySql(
            f"select sum(show_num) from analysis_zjtx_push_rate t where 1=1 and LENGTH(t.ref_date) = 10  ")
        # 获取所有追剧提醒 展示设备量
        cumulateDeviceNum = selectNumBySql(
            f"select sum(show_device_num) from analysis_zjtx_push_rate t where 1=1 and LENGTH(t.ref_date) = 10  ")
        # 计算 当天点开剧集的数量
        msgDict = {'action': 'ok'}
        okNum = selectNumBySql(
            f"select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'suRemindFin'  and JSON_EXTRACT(msg,'$.action') ='ok' and  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}'")
        # 计算 当天息屏及自动关机的数量
        exitNum = selectNumBySql(
            f" select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'suRemindFin' and (JSON_EXTRACT(t.msg,'$.action') = 'poweroff' or JSON_EXTRACT(t.msg,'$.action') = 'auto')   and  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}' ")
        #计算进入主页的数量
        homeNum = selectNumBySql(
            f" select count(1) from log_common_service_{yyMMddDate} t where t.`key` = 'suRemindFin' and (JSON_EXTRACT(t.msg,'$.action') = 'leave' or JSON_EXTRACT(t.msg,'$.action') = 'ok')   and  DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{date}' ")
        # 计算剧集提醒推送成功率
        okRate = getRateByXY(okNum,newPushNum)
        exitRate = getRateByXY(exitNum,newPushNum)
        homeRate = getRateByXY(homeNum,newPushNum)

        if countNum == 0:
            logDetail =f"{date}日新增-消息等待展示数量为{moviesUpdateNum},追剧订阅量为{subscribeNum},追剧提醒推送数量为{newPushNum},设备量为{newActiveIdNum},当日订阅总量{subscribeNum},开机率为{showRate}," \
                       f"\n推送累计次数为{cumulatePushNum},累计设备量{cumulateDeviceNum},进入主页的数量为{homeNum},比例为{homeRate},播放剧集的数量为{okNum},比例为{okRate},自动息屏或用户主动关机的数量为{exitNum},比例为{exitRate}"
            print(logDetail)
            # 插入追剧提醒推送成功率
            insertSql = f"""insert into analysis_zjtx_push_rate(movies_update_num,show_num,show_device_num,show_device_rate,cumulate_num,cumulate_device_num,home_num,home_rate,play_num,play_rate,shutdown_num,shutdown_rate,ref_date) 
                value({moviesUpdateNum},{newPushNum},{newActiveIdNum},{showRate},{cumulatePushNum},{cumulateDeviceNum},{homeNum},{homeRate},{okNum},{okRate},{exitNum},{exitRate},'{date}')"""
            cur.execute(insertSql)
        else:
            logDetail = f"{date}日更新-消息等待展示数量为{moviesUpdateNum},追剧订阅量为{subscribeNum},追剧提醒推送数量为{newPushNum},设备量为{newActiveIdNum},当日订阅总量{subscribeNum},开机率为{showRate}," \
                        f"\n推送累计次数为{cumulatePushNum},累计设备量{cumulateDeviceNum},进入主页的数量为{homeNum},比例为{homeRate},播放剧集的数量为{okNum},比例为{okRate},自动息屏或用户主动关机的数量为{exitNum},比例为{exitRate}"
            print(logDetail)
            # 更新追剧提醒推送成功率
            updateSql = f"""
                update analysis_zjtx_push_rate set movies_update_num = {moviesUpdateNum} ,show_num = {newPushNum},show_device_num = {newActiveIdNum} ,show_device_rate={showRate},cumulate_num = {cumulatePushNum},cumulate_device_num = {cumulateDeviceNum},home_num = {homeNum} ,home_rate = {homeRate},play_num = {okNum},play_rate = {okRate},
                shutdown_num = {exitNum} ,shutdown_rate = {exitRate} ,update_time = now()  where  ref_date = '{date}' 
            """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 数据分析-早间开机-服务推送成功率 analysis_zjtx_push_rate --月度
def insertPushRateMonth(toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zjtx_push_rate 表是否已经存在当天 数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_zjtx_push_rate t where 1=1  and t.ref_date =  '{toMonthDay}'")
        # 消息等待展示/追了剧且当日剧集有更新的设备量
        moviesUpdateNum = getSumNumByTableColumnNameAndDate('analysis_zjtx_push_rate', 'movies_update_num', toMonthDay)
        # 获取当月追剧提醒 展示次数
        newPushNum = getSumNumByTableColumnNameAndDate('analysis_zjtx_push_rate', 'show_num', toMonthDay)
        # 获取当月追剧提醒 展示设备数(去重)
        newActiveIdNum = getSumNumByTableColumnNameAndDate('analysis_zjtx_push_rate', 'show_device_num', toMonthDay)
        # 获取所有追剧提醒 展示次数
        cumulatePushNum = selectNumBySql(
            f"select sum(show_num) from analysis_zjtx_push_rate t where 1=1 and LENGTH(t.ref_date) = 10  ")
        # 获取所有追剧提醒 展示设备量
        cumulateDeviceNum = selectNumBySql(
            f"select sum(show_device_num) from analysis_zjtx_push_rate t where 1=1 and LENGTH(t.ref_date) = 10  ")
        #计算 当月进入主页的数量
        homeNum = getSumNumByTableColumnNameAndDate('analysis_zjtx_push_rate', 'home_num', toMonthDay)
        # 计算 当月点开剧集的数量
        okNum = getSumNumByTableColumnNameAndDate('analysis_zjtx_push_rate', 'play_num', toMonthDay)
        # 计算 当月息屏的数量
        exitNum = getSumNumByTableColumnNameAndDate('analysis_zjtx_push_rate', 'shutdown_num', toMonthDay)
        # 计算剧集提醒推送成功率
        okRate = getRateByXY(okNum, newPushNum)
        exitRate = getRateByXY(exitNum, newPushNum)
        homeRate = getRateByXY(homeNum, newPushNum)

        if countNum == 0:
            logDetail = f"{toMonthDay}月新增-消息等待展示数量为{moviesUpdateNum},追剧提醒推送数量为{newPushNum},设备量为{newActiveIdNum},推送累计次数为{cumulatePushNum},累计设备量{cumulateDeviceNum},进入主页的数量为{homeNum}," \
                        f"比例为{homeRate},播放剧集的数量为{okNum},比例为{okRate},自动息屏或用户主动关机的数量为{exitNum},比例为{exitRate}"
            print(logDetail)
            # 插入追剧提醒推送成功率
            insertSql = f"""insert into analysis_zjtx_push_rate(movies_update_num,show_num,show_device_num,cumulate_num,cumulate_device_num,home_num,home_rate,play_num,play_rate,shutdown_num,shutdown_rate,ref_date) 
                value({moviesUpdateNum},{newPushNum},{newActiveIdNum},{cumulatePushNum},{cumulateDeviceNum},{homeNum},{homeRate},{okNum},{okRate},{exitNum},{exitRate},'{toMonthDay}')"""
            cur.execute(insertSql)
        else:
            logDetail = f"{toMonthDay}月更新-消息等待展示数量为{moviesUpdateNum},追剧提醒推送数量为{newPushNum},设备量为{newActiveIdNum},推送累计次数为{cumulatePushNum},累计设备量{cumulateDeviceNum},进入主页的数量为{homeNum}," \
                        f"比例为{homeRate},播放剧集的数量为{okNum},比例为{okRate},自动息屏或用户主动关机的数量为{exitNum},比例为{exitRate}"
            print(logDetail)
            # 更新追剧提醒推送成功率
            updateSql = f"""
                update analysis_zjtx_push_rate set movies_update_num = {moviesUpdateNum} ,show_num = {newPushNum},show_device_num = {newActiveIdNum} ,cumulate_num = {cumulatePushNum},cumulate_device_num = {cumulateDeviceNum},home_num = {homeNum} ,home_rate = {homeRate},play_num = {okNum},play_rate = {okRate},
                shutdown_num = {exitNum} ,shutdown_rate = {exitRate} ,update_time = now()  where  ref_date = '{toMonthDay}' 
            """
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 49
        jobName = '核心指标-追剧提醒-服务推送成功率'
        tableName = 'analysis_zjtx_push_rate'
        # 插入日志表
        saveLogSql = f"""insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value({jobId},'{jobName}','{detail}','{tableName}','{ref_date}')"""
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    d = 1
    date = getdate(d)
    yyMMddDate = getdateyyMMdd(d)
    monthdate = getMonthdate(d)
    print (f"{date}日期,更新<数据分析-追剧提醒-服务推送成功率>报表")
    insertPushRate(date)
    insertPushRateMonth(monthdate)

